﻿using Data.Initializers;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Data.Repositories
{
    public class SanPhamRepository
    {
        private SqlConnection _connect;
        private SqlDataAdapter _sqlAdapter;

        public DataTable DanhSachSanPham(long idloai)
        {
            try
            {
                ConnectDB.CloseConnection(_connect);
                _connect = new SqlConnection();
                _connect = ConnectDB.GetSQlConnection(_connect);
                var cm = _connect.CreateCommand();
                cm.CommandText = "dbo.C_DANHSACH_SANPHAM";
                cm.Parameters.Add("@idloai", SqlDbType.BigInt).Value = idloai;
                cm.CommandType = CommandType.StoredProcedure;
                var tableGs = new DataTable();
                _sqlAdapter = new SqlDataAdapter(cm);
                _sqlAdapter.Fill(tableGs);
                return tableGs;
            }
            catch (Exception)
            {
                return null;
            }
            finally
            {
                ConnectDB.CloseConnection(_connect);
            }
        }
        public DataTable TimKiemSanPham(string tukhoa, long giatu, long giaden)
        {
            try
            {
                ConnectDB.CloseConnection(_connect);
                _connect = new SqlConnection();
                _connect = ConnectDB.GetSQlConnection(_connect);
                var cm = _connect.CreateCommand();
                cm.CommandText = "dbo.C_TIMKIEM_SANPHAM";
                cm.Parameters.Add("@tukhoa", SqlDbType.NVarChar).Value = tukhoa;
                cm.Parameters.Add("@giatu", SqlDbType.BigInt).Value = giatu;
                cm.Parameters.Add("@giaden", SqlDbType.BigInt).Value = giaden;
                cm.CommandType = CommandType.StoredProcedure;
                var tableGs = new DataTable();
                _sqlAdapter = new SqlDataAdapter(cm);
                _sqlAdapter.Fill(tableGs);
                return tableGs;
            }
            catch (Exception)
            {
                return null;
            }
            finally
            {
                ConnectDB.CloseConnection(_connect);
            }
        }
        public DataTable DanhSachLoaiSanPham()
        {
            try
            {
                ConnectDB.CloseConnection(_connect);
                _connect = new SqlConnection();
                _connect = ConnectDB.GetSQlConnection(_connect);
                var cm = _connect.CreateCommand();
                cm.CommandText = "dbo.C_DANHSACH_LOAISANPHAM";
                cm.CommandType = CommandType.StoredProcedure;
                var tableGs = new DataTable();
                _sqlAdapter = new SqlDataAdapter(cm);
                _sqlAdapter.Fill(tableGs);
                return tableGs;
            }
            catch (Exception)
            {
                return null;
            }
            finally
            {
                ConnectDB.CloseConnection(_connect);
            }
        }
        public DataTable ChiTietSanPham(long IdSanPham)
        {
            try
            {
                ConnectDB.CloseConnection(_connect);
                _connect = new SqlConnection();
                _connect = ConnectDB.GetSQlConnection(_connect);
                var cm = _connect.CreateCommand();
                cm.CommandText = "dbo.C_CHITIET_SANPHAM";
                cm.Parameters.Add("@id_san_pham", SqlDbType.BigInt).Value = IdSanPham;
                cm.CommandType = CommandType.StoredProcedure;
                var tableGs = new DataTable();
                _sqlAdapter = new SqlDataAdapter(cm);
                _sqlAdapter.Fill(tableGs);
                return tableGs;
            }
            catch (Exception)
            {
                return null;
            }
            finally
            {
                ConnectDB.CloseConnection(_connect);
            }
        }
        public DataTable ChiTietNoiDungSanPham(long IdSanPham, int IdLoai)
        {
            try
            {
                ConnectDB.CloseConnection(_connect);
                _connect = new SqlConnection();
                _connect = ConnectDB.GetSQlConnection(_connect);
                var cm = _connect.CreateCommand();
                cm.CommandText = "dbo.C_CHITIET_ND_SANPHAM";
                cm.Parameters.Add("@id_san_pham", SqlDbType.BigInt).Value = IdSanPham;
                cm.Parameters.Add("@id_loai", SqlDbType.Int).Value = IdLoai;
                cm.CommandType = CommandType.StoredProcedure;
                var tableGs = new DataTable();
                _sqlAdapter = new SqlDataAdapter(cm);
                _sqlAdapter.Fill(tableGs);
                return tableGs;
            }
            catch (Exception)
            {
                return null;
            }
            finally
            {
                ConnectDB.CloseConnection(_connect);
            }
        }

        public string CapNhatLoaiSanPham(LoaiSanPham lsp)
        {
            try
            {

                if (lsp.Id != 0)
                {
                    ConnectDB.CloseConnection(_connect);
                    _connect = new SqlConnection();
                    _connect = ConnectDB.GetSQlConnection(_connect);
                    var cm = _connect.CreateCommand();
                    cm.CommandText = "dbo.C_DELETE_LOAISANPHAM";
                    cm.CommandType = CommandType.StoredProcedure;
                    cm.Parameters.Add("@id", SqlDbType.BigInt).Value = lsp.Id;
                    cm.Parameters.Add("@NewQuoteNumber", SqlDbType.Int).Direction = ParameterDirection.Output;
                    //cm.Parameters.Add(newReqNumber);
                    cm.ExecuteNonQuery();
                    string NewQuoteNumber = cm.Parameters["@NewQuoteNumber"].Value.ToString();
                    if (NewQuoteNumber == "0" || NewQuoteNumber == "") throw new Exception();
                }
                else
                {
                    //them moi
                    ConnectDB.CloseConnection(_connect);
                    _connect = new SqlConnection();
                    _connect = ConnectDB.GetSQlConnection(_connect);
                    var cm = _connect.CreateCommand();
                    cm.CommandText = "dbo.C_INSERT_LOAISANPHAM";
                    cm.CommandType = CommandType.StoredProcedure;
                    cm.Parameters.Add("@ten", SqlDbType.NVarChar).Value = lsp.Ten;
                    cm.Parameters.Add("@trang_thai", SqlDbType.Int).Value = 0;
                    cm.Parameters.Add("@NewQuoteNumber", SqlDbType.BigInt).Direction = ParameterDirection.Output;
                    cm.ExecuteNonQuery();
                    string NewQuoteNumber = cm.Parameters["@NewQuoteNumber"].Value.ToString();
                    if (NewQuoteNumber == "0" || NewQuoteNumber == "") throw new Exception();
                }
                return "Thành công";
            }
            catch (Exception)
            {
                return null;
            }
            finally
            {
                ConnectDB.CloseConnection(_connect);
            }
        }

        public string CapNhatSanPham(SanPham sp)
        {
            try
            {
                if (sp.Id != 0)
                {
                    if (sp.Ten != null)
                    {
                        ConnectDB.CloseConnection(_connect);
                        _connect = new SqlConnection();
                        _connect = ConnectDB.GetSQlConnection(_connect);
                        var cm = _connect.CreateCommand();
                        cm.CommandText = "dbo.C_UPDATE_SANPHAM";
                        cm.CommandType = CommandType.StoredProcedure;
                        cm.Parameters.Add("@id", SqlDbType.BigInt).Value = sp.Id;
                        cm.Parameters.Add("@ten", SqlDbType.NVarChar).Value = sp.Ten;
                        cm.Parameters.Add("@anh", SqlDbType.NVarChar).Value = sp.Anh;
                        cm.Parameters.Add("@gia", SqlDbType.BigInt).Value = sp.Gia;
                        cm.Parameters.Add("@soluong", SqlDbType.BigInt).Value = sp.SoLuong;
                        cm.Parameters.Add("@NewQuoteNumber", SqlDbType.Int).Direction = ParameterDirection.Output;
                        //cm.Parameters.Add(newReqNumber);
                        cm.ExecuteNonQuery();
                        string NewQuoteNumber = cm.Parameters["@NewQuoteNumber"].Value.ToString();
                        if (NewQuoteNumber == "0" || NewQuoteNumber == "") throw new Exception();
                    }
                    else
                    {
                        ConnectDB.CloseConnection(_connect);
                        _connect = new SqlConnection();
                        _connect = ConnectDB.GetSQlConnection(_connect);
                        var cm = _connect.CreateCommand();
                        cm.CommandText = "dbo.C_DELETE_SANPHAM";
                        cm.CommandType = CommandType.StoredProcedure;
                        cm.Parameters.Add("@id", SqlDbType.BigInt).Value = sp.Id;
                        cm.Parameters.Add("@NewQuoteNumber", SqlDbType.Int).Direction = ParameterDirection.Output;
                        //cm.Parameters.Add(newReqNumber);
                        cm.ExecuteNonQuery();
                        string NewQuoteNumber = cm.Parameters["@NewQuoteNumber"].Value.ToString();
                        if (NewQuoteNumber == "0" || NewQuoteNumber == "") throw new Exception();
                    }
                }
                else
                {
                    ConnectDB.CloseConnection(_connect);
                    _connect = new SqlConnection();
                    _connect = ConnectDB.GetSQlConnection(_connect);
                    var cm = _connect.CreateCommand();
                    cm.CommandText = "dbo.C_INSERT_SANPHAM";
                    cm.CommandType = CommandType.StoredProcedure;
                    cm.Parameters.Add("@ten", SqlDbType.NVarChar).Value = sp.Ten;
                    cm.Parameters.Add("@anh", SqlDbType.NVarChar).Value = sp.Anh;
                    cm.Parameters.Add("@gia", SqlDbType.BigInt).Value = sp.Gia;
                    cm.Parameters.Add("@soluong", SqlDbType.BigInt).Value = sp.SoLuong;
                    cm.Parameters.Add("@loai", SqlDbType.BigInt).Value = sp.IdLoai;
                    cm.Parameters.Add("@trang_thai", SqlDbType.Int).Value = 0;
                    cm.Parameters.Add("@NewQuoteNumber", SqlDbType.BigInt).Direction = ParameterDirection.Output;
                    cm.ExecuteNonQuery();
                    string NewQuoteNumber = cm.Parameters["@NewQuoteNumber"].Value.ToString();
                    if (NewQuoteNumber == "0" || NewQuoteNumber == "") throw new Exception();
                }
                return "Thành công";
            }
            catch (Exception)
            {
                return null;
            }
            finally
            {
                ConnectDB.CloseConnection(_connect);
            }
        }

        public string CapNhatChiTietSanPham(SanPhamAnhCT anhct, SanPhamNoiDungCT noidungct)
        {
            try
            {

                if (anhct != null)
                {
                    if (anhct.IdChiTiet != 0)
                    {
                        ConnectDB.CloseConnection(_connect);
                        _connect = new SqlConnection();
                        _connect = ConnectDB.GetSQlConnection(_connect);
                        var cm = _connect.CreateCommand();
                        cm.CommandText = "dbo.C_DELETE_CHITIETSANPHAM";
                        cm.CommandType = CommandType.StoredProcedure;
                        cm.Parameters.Add("@id", SqlDbType.BigInt).Value = anhct.IdChiTiet;
                        cm.Parameters.Add("@NewQuoteNumber", SqlDbType.Int).Direction = ParameterDirection.Output;
                        //cm.Parameters.Add(newReqNumber);
                        cm.ExecuteNonQuery();
                        string NewQuoteNumber = cm.Parameters["@NewQuoteNumber"].Value.ToString();
                        if (NewQuoteNumber == "0" || NewQuoteNumber == "") throw new Exception();
                    }
                    else
                    {
                        ConnectDB.CloseConnection(_connect);
                        _connect = new SqlConnection();
                        _connect = ConnectDB.GetSQlConnection(_connect);
                        var cm = _connect.CreateCommand();
                        cm.CommandText = "dbo.C_INSERT_SANPHAMCHITIET";
                        cm.CommandType = CommandType.StoredProcedure;
                        cm.Parameters.Add("@idsanpham", SqlDbType.NVarChar).Value = anhct.IdSanPham;
                        cm.Parameters.Add("@noidung", SqlDbType.NVarChar).Value = anhct.LinkAnh;
                        cm.Parameters.Add("@loai", SqlDbType.BigInt).Value = 1;
                        cm.Parameters.Add("@trang_thai", SqlDbType.Int).Value = 0;
                        cm.Parameters.Add("@NewQuoteNumber", SqlDbType.BigInt).Direction = ParameterDirection.Output;
                        cm.ExecuteNonQuery();
                        string NewQuoteNumber = cm.Parameters["@NewQuoteNumber"].Value.ToString();
                        if (NewQuoteNumber == "0" || NewQuoteNumber == "") throw new Exception();
                    }

                }
                else
                {
                    if (noidungct != null)
                    {
                        if (noidungct.IdChiTiet != 0)
                        {
                            ConnectDB.CloseConnection(_connect);
                            _connect = new SqlConnection();
                            _connect = ConnectDB.GetSQlConnection(_connect);
                            var cm = _connect.CreateCommand();
                            cm.CommandText = "dbo.C_DELETE_CHITIETSANPHAM";
                            cm.CommandType = CommandType.StoredProcedure;
                            cm.Parameters.Add("@id", SqlDbType.BigInt).Value = noidungct.IdChiTiet;
                            cm.Parameters.Add("@NewQuoteNumber", SqlDbType.Int).Direction = ParameterDirection.Output;
                            //cm.Parameters.Add(newReqNumber);
                            cm.ExecuteNonQuery();
                            string NewQuoteNumber = cm.Parameters["@NewQuoteNumber"].Value.ToString();
                            if (NewQuoteNumber == "0" || NewQuoteNumber == "") throw new Exception();
                        }
                        else
                        {
                            ConnectDB.CloseConnection(_connect);
                            _connect = new SqlConnection();
                            _connect = ConnectDB.GetSQlConnection(_connect);
                            var cm = _connect.CreateCommand();
                            cm.CommandText = "dbo.C_INSERT_SANPHAMCHITIET";
                            cm.CommandType = CommandType.StoredProcedure;
                            cm.Parameters.Add("@idsanpham", SqlDbType.NVarChar).Value = noidungct.IdSanPham;
                            cm.Parameters.Add("@noidung", SqlDbType.NVarChar).Value = noidungct.NoiDung;
                            cm.Parameters.Add("@loai", SqlDbType.BigInt).Value = 2;
                            cm.Parameters.Add("@trang_thai", SqlDbType.Int).Value = 0;
                            cm.Parameters.Add("@NewQuoteNumber", SqlDbType.BigInt).Direction = ParameterDirection.Output;
                            cm.ExecuteNonQuery();
                            string NewQuoteNumber = cm.Parameters["@NewQuoteNumber"].Value.ToString();
                            if (NewQuoteNumber == "0" || NewQuoteNumber == "") throw new Exception();
                        }
                    }
                }

                return "Thành công";
            }
            catch (Exception)
            {
                return null;
            }
            finally
            {
                ConnectDB.CloseConnection(_connect);
            }
        }
    }
}
